package cn.fancy.sdk.login.utils;

import java.util.ArrayList;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import cn.fancy.sdk.common.utils.Logger;
import cn.fancy.sdk.common.utils.SE;

public class DBHelper extends SQLiteOpenHelper {

	private static final String CMGE_DB = "Guild_sdk";

	private static final int DB_VERSION = 29; // 原来为10 测试29

	public static final String TABLE_NAME = "native";

	public static final String USERID = "user_id";
	public static final String USERNAME = "user_name";
	public static final String PASSWORD = "user_password";
	public static final String PASSWORDMD = "user_passwordmd";

	/* 1 有效， 0无效 */
	public static final String VALID = "is_valid";

	public static final String AUTOLOGIN = "auto_login";
	public static final String LASTLOGINTIME = "last_login_time";

	private static DBHelper mInstance = null;

	public static final String Table_Sign = "only_mobile_table_new";
	public static final String NumberSig = "_id";
	public static final String Imkey = "mobile_sign";
	public static final String Mac_timetable = "mac_create_time";
	public static final String timekey = "ctime";

	public static final String AndroidId_Table = "only_androidid_table";
	public static final String ANumberSig = "_id";
	public static final String AkeySig = "android_id";

	public static final String Imei_table = "imei_table";
	public static final String imeikey = "imei";
	public static final String ctimekey = "ctime";
	
	public DBHelper(Context context) {
		super(context, CMGE_DB, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 创建用户基本信息表
		String sql_create_db = "create table if not exists " + TABLE_NAME
				+ " ( _id integer primary key autoincrement , " + USERID
				+ " varchar(100)," + USERNAME + " varchar(100)," + PASSWORD
				+ " varchar(100)," + VALID + " integer, " + AUTOLOGIN
				+ " integer, " + LASTLOGINTIME + " integer);";

		db.execSQL(sql_create_db);

		// 创建 mac 表
		String sql_create_signtable = "create table if not exists  "
				+ Table_Sign + "  (  " + NumberSig + " integer primary key , "
				+ Imkey + " varchar(100) );";
		db.execSQL(sql_create_signtable);

		// 创建mac时间表
		String mac_ctime_table = "create table if not exists  " + Mac_timetable
				+ "  (  " + NumberSig + " integer primary key , " + timekey
				+ " varchar(100) );";
		db.execSQL(mac_ctime_table);

		// 创建 androidid 表
		String sql_create_androidtable = "create table if not exists  "
				+ AndroidId_Table + "  (  " + ANumberSig
				+ " integer primary key , " + AkeySig + " varchar(100) );";
		db.execSQL(sql_create_androidtable);

		// 创建imei表
		String sql_imei_tabel = "create table if not exists  " + Imei_table
				+ "  (  " + ANumberSig + " integer primary key , " + imeikey
				+ " varchar(100) ," + ctimekey + " varchar(100));";
		db.execSQL(sql_imei_tabel);
	}
	
	public synchronized static DBHelper getInstance(Context context) {
		if (mInstance == null) {
			mInstance = new DBHelper(context);
		}
		return mInstance;
	};

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		onCreate(db);
	}

	public synchronized ArrayList<SDK_ACCOUNT> getAllAccounts() {
		ArrayList<SDK_ACCOUNT> accts = new ArrayList<SDK_ACCOUNT>();

		Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null, null,
				null, null, null, LASTLOGINTIME + " desc");

		if (cs.moveToFirst()) {
			while (!cs.isAfterLast()) {
				SDK_ACCOUNT temp = SDK_ACCOUNT.buildSdkAccountFromCursor(cs);

				if (temp != null) {
					Logger.print_red("DBHelper.getAllAccounts() - ",
							temp.toString());
					accts.add(temp);
				}

				cs.moveToNext();
			}
		}
		cs.close();

		return accts;
	}

	/**
	 * 
	 * 将sqlite数据库中的用户账号设为无效， 无效并不是删除账号，而是不再将账号提供用户选择
	 * 
	 * @param username
	 *            用户账号名
	 * @return -1 更改失败 or 影响的账号数
	 */
	public synchronized int setAccountInvalid(String username) {
		if (username == null || "".equals(username))
			return -1;

		int result = -1;

		try {

			String encrypted_user_name = SE.DO_ENCRYPT_BASE64(username);

			ContentValues cvalue = new ContentValues();
			cvalue.put(VALID, 0);

			result = this.getWritableDatabase().update(TABLE_NAME, cvalue,
					USERNAME + "=?", new String[] { encrypted_user_name });

		} catch (Exception e) {

			e.printStackTrace();

		}

		return result;

	}

	/**
	 * 返回所有可提供用户选择的账号
	 * 
	 * @return ArrayList<SDK_ACCOUNT>
	 */
	public synchronized ArrayList<SDK_ACCOUNT> getAllValidAccounts() {
		ArrayList<SDK_ACCOUNT> accts = new ArrayList<SDK_ACCOUNT>();

		Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null,
				VALID + "=1", null, null, null, LASTLOGINTIME + " desc");

		if (cs.moveToFirst()) {
			while (!cs.isAfterLast()) {

				SDK_ACCOUNT temp = SDK_ACCOUNT.buildSdkAccountFromCursor(cs);
				if (temp != null) {
					accts.add(temp);
				}
				cs.moveToNext();
			}
		}
		cs.close();

		return accts;
	}

	public synchronized SDK_ACCOUNT getAccountByName(String username) {
		SDK_ACCOUNT account = new SDK_ACCOUNT();
		if (username == null || "".equals(username))
			return null;

		String encrypted_user_name = SE.DO_ENCRYPT_BASE64(username
				);

		Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null,
				USERNAME + "=?", new String[] { encrypted_user_name }, null,
				null, null);

		if (cs != null && cs.moveToFirst()) {
			account.auto_login = cs.getInt(cs.getColumnIndex(AUTOLOGIN));
			account.is_valid = cs.getInt(cs.getColumnIndex(VALID));
			account.last_login_time = cs.getLong(cs
					.getColumnIndex(LASTLOGINTIME));
			account.user_id = cs.getString(cs.getColumnIndex(USERID));
			account.user_name = SE.DO_DECRYPT_BASE64(cs.getString(cs
					.getColumnIndex(USERNAME)));
			account.user_password = SE.DO_DECRYPT_BASE64(cs.getString(cs
					.getColumnIndex(PASSWORD)));

		}

		return account;
	}

	@SuppressLint("DefaultLocale")
	public synchronized boolean isAccountPresent(SDK_ACCOUNT acct) {
		// 一键登陆没密码
		if ("".equals(acct.user_password)) {
			String lowStr = acct.user_name;
			String encrypted_user_name = SE.DO_ENCRYPT_BASE64(lowStr);

			Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null,
					USERNAME + "=?", new String[] { encrypted_user_name },
					null, null, null);
			if (cs.getCount() < 1) {
				// 查没此号
				cs.close();
				return false;
			} else {
				cs.close();
				return true;
			}
		}

		if (acct == null || acct.isEmpty()) {
			return false;
		}

		String lowStr = acct.user_name;
		String encrypted_user_name = SE.DO_ENCRYPT_BASE64(lowStr);

		Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null,
				USERNAME + "=?", new String[] { encrypted_user_name }, null,
				null, null);

		if (cs.getCount() < 1) {
			Logger.e("ssssss", "false");
			cs.close();
			return false;
		} else {
			Logger.e("ssssss", "true");
			cs.close();
			return true;
		}
	}

	public synchronized long insertNewAccount(SDK_ACCOUNT acct) {
		Logger.d("DBHelper", "insertNewAccount");
		if (acct == null || acct.isEmpty()) {
			return -1;
		}

		long id = -1;

		try {
			id = this.getWritableDatabase().insert(TABLE_NAME, null,
					acct.toContenValues());

			if (id > 0) {
				Logger.e("DBHelper",
						"DBHelper.insertNewAccount() - " + acct.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
		return id;
	}

	public synchronized int updateAccount(SDK_ACCOUNT acct) {
		Logger.d("DBHelper", "updateAccount:" + acct);
		if (acct == null || acct.isEmpty()) {
			// 一键注册无密码，数据库有此帐号,把这个帐号移到首位
			String lowStr = acct.user_name;
			String encrypted_user_name = SE.DO_ENCRYPT_BASE64(lowStr);
			Log.e("isaccmovetofirst", "isaccmovetofirst" + lowStr);

			Cursor cs = this.getWritableDatabase().query(TABLE_NAME, null,
					USERNAME + "=?", new String[] { encrypted_user_name },
					null, null, null);
			// 修改登陆时间
			cs.moveToFirst();
			// 要先把加密后的密码解密
			if ("".equals(acct.user_password)) {
				int oldpassPosition = cs.getColumnIndex(PASSWORD);
				String oldpass = cs.getString(oldpassPosition);
				oldpass = SE.DO_DECRYPT_BASE64(oldpass);
				Log.e("oldpass", "oldpass" + oldpass);
				acct.user_password = oldpass;
			}

			int rows2 = 0;
			try {
				rows2 = this.getWritableDatabase().update(TABLE_NAME,
						acct.toContenValues(), USERNAME + "=?",
						new String[] { encrypted_user_name });
				if (rows2 > 0) {
					Logger.print_red("DBHelper.updateAccount(...)",
							"update a record");
				}
			} catch (Exception e) {
				e.printStackTrace();
				return -1;
			}
			return rows2;

		}

		int rows = 0;

		try {
			String encrypted_user_name = SE.DO_ENCRYPT_BASE64(acct.user_name);
			rows = this.getWritableDatabase().update(TABLE_NAME,
					acct.toContenValues(), USERNAME + "=?",
					new String[] { encrypted_user_name });
			if (rows > 0) {
				Logger.print_red("DBHelper.updateAccount(...)",
						"update a record");
			}
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}

		return rows;
	}

	public synchronized boolean deleteAccountByUsername(String username) {
		if (username == null || "".equals(username))
			return false;

		String encrypted_user_name = SE.DO_ENCRYPT_BASE64(username
				);

		return this.getWritableDatabase().delete(TABLE_NAME, USERNAME + "=?",
				new String[] { encrypted_user_name }) > 0;
	}

	public synchronized boolean deleteAllAccounts() {
		return this.getWritableDatabase().delete(TABLE_NAME, null, null) > 0;
	}

	public static class SDK_ACCOUNT {
		public String user_id = "";
		public String user_name = "";
		public String user_password = "";

		/* 默认为有效用户 */
		public int is_valid = 1;

		/* 默认为自动登录 */
		public int auto_login = 1;

		public long last_login_time = 0;

		public ContentValues toContenValues() {

			ContentValues value = new ContentValues();

			value.put(USERID, user_id);

			// 加密编码保存用户名
			value.put(USERNAME, SE.DO_ENCRYPT_BASE64(user_name));

			// 加密编码保存密码
			value.put(PASSWORD, SE.DO_ENCRYPT_BASE64(user_password));

			value.put(VALID, is_valid);

			value.put(AUTOLOGIN, auto_login);

			if (last_login_time == 0) {
				value.put(LASTLOGINTIME, System.currentTimeMillis());
			} else {
				value.put(LASTLOGINTIME, last_login_time);
			}
			return value;
		}

		public static SDK_ACCOUNT buildSdkAccountFromCursor(Cursor cursor) {
			SDK_ACCOUNT acct = null;

			if (cursor != null && !cursor.isAfterLast()) {

				acct = new SDK_ACCOUNT();

				acct.user_id = cursor.getString(cursor.getColumnIndex(USERID));

				// 用户名解码解密
				acct.user_name = SE.DO_DECRYPT_BASE64(cursor.getString(cursor
						.getColumnIndex(USERNAME)));

				// 密码解码解密
				acct.user_password = SE.DO_DECRYPT_BASE64(cursor
						.getString(cursor.getColumnIndex(PASSWORD)));

				// 用户登录的时候没有要求记住密码
				if (acct.user_password.trim().equals("null"))
					acct.user_password = "";

				acct.is_valid = cursor.getInt(cursor.getColumnIndex(VALID));

				acct.auto_login = cursor.getInt(cursor
						.getColumnIndex(AUTOLOGIN));

				acct.last_login_time = cursor.getLong(cursor
						.getColumnIndex(LASTLOGINTIME));
			}

			return acct;
		}

		@Override
		public String toString() {
			return "user_id=" + user_id + " " + "user_name=" + user_name + " "
					+ "user_password=" + user_password + " " + "auto_login="
					+ auto_login + " " + "is_valid=" + is_valid + " "
					+ "last_login_time=" + last_login_time + "\n";
		}

		public boolean isEmpty() {
			return "".equals(user_name) || "".equals(user_password);
		}

	}

}